"""
Case Type   : 事务控制
Case Name   : 将DDL放在匿名块变量中执行
Create At   : 2020/12/02 
Owner       : opentestcase015
Description :
    1.使用匿名块对主机数据进行数据写入
    2.查看备机数据，确认数据写入成功
Expect      :
    1.使用匿名块对主机数据进行数据写入，成功
    2.查看备机数据，确认数据写入成功，成功
History     :
    2020/12/14 modifi by opentestcase007 类名与方法名重命名为与用例相关的名称，修改清理环境步骤
    2023/09/07 modify by opentestcase024: 优化整体用例，增加主备同步等待，确保备机数据同步
    2024/6/18 modified by peilinqian 用例适配资源池化
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class TransactionFile(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.constant = Constant()
        self.sta1_sh = CommonSH('Standby1DbUser')
        self.tb_name = 't_dml_transaction_case0014'

    def test_transaction_file(self):
        step_txt = '----step1:使用匿名块对主机数据进行数据写入 expect:创建成功----'
        self.log.info(step_txt)
        sql_cmd = f"declare\n" \
            f"v_sql varchar;\n" \
            f"begin\n" \
            f"v_sql := 'drop table if exists {self.tb_name};" \
            f"create table {self.tb_name}(sk integer,id char(16)," \
            f"name varchar(20),sq_ft integer);';\n" \
            f"execute immediate v_sql ;" \
            f"end;"
        create_result = pri_sh.execut_db_sql(sql_cmd)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG,
                      create_result, '执行失败' + step_txt)

        step_txt = '----step2:查看备机数据，确认数据写入成功 expect:写入成功----'
        self.log.info(step_txt)
        select_sql = f"select * from {self.tb_name};"
        select_result = pri_sh.execut_db_sql(select_sql)
        self.log.info(select_result)
        self.assertIn("0 rows", select_result, '执行失败' + step_txt)
        self.assertNotIn(self.constant.NOT_EXIST, select_result)

    def tearDown(self):
        self.log.info('----清理环境----')
        step_txt = '----删除表; expect:删除成功----'
        self.log.info(step_txt)
        drop_sql = f"drop table if exists {self.tb_name};"
        drop_result = pri_sh.execut_db_sql(drop_sql)
        self.log.info(drop_result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_result,
                      '执行失败' + step_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
